/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Metodos;

import Atributos.Curso;
import Atributos.Serie;
import BD.CriaConexao;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

/**
 *
 * @author Andre
 */
public class CursoMet {
     private Connection conexao;
     private List<Serie> listaS;
     
    public CursoMet(){
        try {
            this.conexao=(Connection) CriaConexao.getConexao();
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    
    
    
    public void AdicionaCurso(Curso c) throws SQLException {
        int id=0;
        int incremento=0;
        String sql = "select * from curso";
        try (PreparedStatement stmt0 = (PreparedStatement) conexao.prepareStatement(sql); ResultSet rs0 = stmt0.executeQuery()) {
            while (rs0.next()) {   
            if(c.getNome().equals(rs0.getString("nome_curso"))){   
            String sql_b = "select id_curso from curso where nome_curso=?";
                    try (PreparedStatement stmt2 = (PreparedStatement) conexao.prepareStatement(sql_b)) {
                        stmt2.setString(1, c.getNome());
                        try (ResultSet rs = stmt2.executeQuery()) {
                            while (rs.next())
                            {
                                id=rs.getInt("id_curso");    
                            }
                        }
                    }
            String sql_c = "insert into descricao (id_curso_desc,descricao_desc) values (?,?)";
                    try (PreparedStatement stmt3 = (PreparedStatement) conexao.prepareStatement(sql_c)) {
                        stmt3.setInt(1, id);        
                        stmt3.setString(2, c.getDescrição());       
                        stmt3.execute();
                    }
            incremento=1;
             }   
            }
        }
        if(incremento==0){
        String sql_a = "insert into curso (nome_curso) values (?)";
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql_a)) {
                stmt.setString(1, c.getNome());        
                stmt.execute();
            }
        String sql_b = "select id_curso from curso where nome_curso=?";
            try (PreparedStatement stmt2 = (PreparedStatement) conexao.prepareStatement(sql_b)) {
                stmt2.setString(1, c.getNome());
                try (ResultSet rs = stmt2.executeQuery()) {
                    while (rs.next())
                    {
                        id=rs.getInt("id_curso");    
                    }
                }
            }
        String sql_c = "insert into descricao (id_curso_desc,descricao_desc) values (?,?)";
            try (PreparedStatement stmt3 = (PreparedStatement) conexao.prepareStatement(sql_c)) {
                stmt3.setInt(1, id);        
                stmt3.setString(2, c.getDescrição());       
                stmt3.execute();
            }
        }
    }
    

    public void Alterar_Curso(Curso curso) throws SQLException {
        if(curso.getNome().equals(curso.getNome_ant()) && !curso.getDescrição().equals(curso.getDesc_ant())){
            System.out.println("entrou na op 1");
            Altera1Opcao(curso);
        }
        
        
        if(!curso.getNome().equals(curso.getNome_ant()) && curso.getDescrição().equals(curso.getDesc_ant())){
             System.out.println("entrou na op 2");
            Altera2Opcao(curso);
             
             
        }
        
        
        if(!curso.getNome().equals(curso.getNome_ant()) && !curso.getDescrição().equals(curso.getDesc_ant())){
             System.out.println("entrou na op 3");          
        Altera3Opcao(curso);    
            
                    
         
        }

           
    }
    
 
    public boolean BuscaCursoseDescriçoesiguais(Curso c)  {
        String sql = "select curso.nome_curso, descricao.descricao_desc from curso, descricao where curso.id_curso=descricao.id_curso_desc and curso.nome_curso=? and descricao.descricao_desc=?";
        int decisao=0;
        try {
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
                stmt.setString(1,c.getNome());
            stmt.setString(2,c.getDescrição());
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        if(rs.getString("nome_curso").equals(c.getNome()) && rs.getString("descricao_desc").equals(c.getDescrição())){
                         decisao=1;   
                        }            
                        
                    }
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
        
            if(decisao==1){
                System.out.println("achou um igual");
                return false;
                
            }else{
                System.out.println("naum achou um igual");
               return true; 
            }
           
    }

    public boolean Buscar_Se_Ha_Curso() {
      String sql = "select nome_curso from curso";
        int decisao=0;
        try {
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql); ResultSet rs = stmt.executeQuery()) {

                
                    System.out.println("entrou aki");
                    if(!rs.next()){
                     decisao=1;  
                        System.out.println("akiii");
                    }            
                    
                
            }
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
        
            if(decisao==1){
                System.out.println("nao encontrou");
                return false;
                
            }else{
                System.out.println("encontrou");
               return true; 
            }
             
    }
    
    
    public String[] Buscar_Nome_Cursos() {
        String sql = "select DISTINCT curso.nome_curso from curso order by nome_curso";
            String[] nome =null;
            
         try {
            
            Statement stmt;
        
            stmt = conexao.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            try (ResultSet rs = stmt.executeQuery(sql)) {
                int i=0;
                    while (rs.next())
                    i++;                
                
                nome = new String[i];
                
                rs.beforeFirst();
                i=0;
                while (rs.next()){
                   nome[i] = rs.getString("nome_curso");
                    i++;
                    
                    
                
                }
            }
            stmt.close();
           
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
          return nome; 
    }
    
    public List<Curso> Buscar_Descricaolist(String a) {
        String sql = "select * from curso,descricao where curso.id_curso=descricao.id_curso_desc and curso.nome_curso like ? order by descricao_desc";
         List<Curso> lista = new ArrayList<>();
         try {
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
                stmt.setString(1, a);
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        Curso c = new Curso();
                        
                        c.setId(rs.getInt("id_curso"));
                        c.setNome(rs.getString("nome_curso"));
                        c.setId_descricao(rs.getInt("id_desc"));
                        c.setId_curso(rs.getInt("id_curso_desc"));
                        c.setDescrição(rs.getString("descricao_desc"));
                        lista.add(c);
                        
                    }
                }
            }
           
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
          return lista;
    }
    
    
    public int Buscar_ID_Curso(String a) {
        String sql = "select id_curso from curso where nome_curso = ?";
        int id=0;
  
        try {
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
                stmt.setString(1, a);
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()){
                        Curso c = new Curso();
                        
                        c.setId(rs.getInt("id_curso"));
                        id=c.getId();
                        
                    }
                }
            }
           
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
          return id;
    }
    
    
    public int Buscar_ID_Descricao(int a,String b,int i) {
        String sql = "select descricao.id_desc,descricao.id_curso_desc from curso,descricao where curso.id_curso=descricao.id_curso_desc "
                + "and descricao.descricao_desc like ? and descricao.id_curso_desc=?";
        int id=0;
        int id_curso=0;
        try {
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
                stmt.setInt(2, a);
                stmt.setString(1, b);
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()){
                        Curso c = new Curso();
                        c.setId_descricao(rs.getInt("id_desc"));
                        c.setId_curso(rs.getInt("id_curso_desc"));
                        
                        id=c.getId_descricao();
                        id_curso=c.getId_curso();
                    }
                }
            }
           
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }if(i==1){
          return id_curso;  
        }else{
          return id;
        }
    }

    private boolean HaMaisDumaLetra(Curso curso) {
                 String sql = "select descricao_desc from descricao where id_curso_desc=?";
        int decisao=0;
        try {
            PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql); 
            stmt.setInt(1, curso.getId());
                    ResultSet rs = stmt.executeQuery();

                
                    System.out.println("entrou aki");
                    while (rs.next()){
                    
                     decisao++;  
                        System.out.println("akiii");
                                
                    } 
                
            
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
        
            if(decisao>1){
                System.out.println("tem mais q uma descricao");
                return true;
                
            }else{
                System.out.println("nao ha mais que uma descricao");
               return false; 
            }
    }

    private int BuscaId(String a) {
        
                String sql = "select id_curso from curso where nome_curso=?";
        int id=0;
  
        try {
            PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql); 
            stmt.setString(1, a);
                    ResultSet rs = stmt.executeQuery();
                
                while (rs.next()){
                    Curso c = new Curso();
                    
                    c.setId(rs.getInt("id_curso"));
                    id=c.getId();
                    
                }
            
           
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
          return id;
    }

    private boolean ONomeExiste(Curso curso) {
        String sql = "select nome_curso from curso where nome_curso=?";
        int decisao=0;
        try {
            PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
            stmt.setString(1, curso.getNome());
                    ResultSet rs = stmt.executeQuery();

                
                    System.out.println("entrou aki");
                    while (rs.next()){
                    if(rs.getString("nome_curso").equals(curso.getNome())){
                     decisao=1;  
                        System.out.println("akiii");
                    }            
                    
                
            }
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
        
            if(decisao==1){
                System.out.println("nao encontrou");
                return true;
                
            }else{
                System.out.println("encontrou");
               return false; 
            }
    }

    private int BuscaIdDesc(Curso curso) {
        String sql = "select id_desc from descricao where id_curso_desc=? and descricao_desc=?";
        int id=0;
  
        try {
            PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql); 
            stmt.setString(2, curso.getDesc_ant());
            stmt.setInt(1, curso.getId());
            ResultSet rs = stmt.executeQuery();
                
                while (rs.next()){
                    Curso c = new Curso();
                    
                    c.setId_descricao(rs.getInt("id_desc"));
                    id=c.getId_descricao();
                    
                }
            
           
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
          return id;
    }
    
   private int BuscaIdDescNovo(Curso curso, int id2)  {
        String sql = "select id_desc from descricao where id_curso_desc=? and descricao_desc=?";
        int id=0;
  
        try {
            PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql); 
            stmt.setString(2, curso.getDescrição());
            stmt.setInt(1, id2);
            ResultSet rs = stmt.executeQuery();
                
                while (rs.next()){
                    Curso c = new Curso();
                    
                    c.setId_descricao(rs.getInt("id_desc"));
                    id=c.getId_descricao();
                    
                }
            
           
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
          return id;
    }
  
    private boolean HouverSerie(Curso curso) {
        String sql = "select nome_serie from serie where id_curso_s=? and id_turma_s=?";
        int decisao=0;
        try {
            PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
            stmt.setInt(1, curso.getId());
            stmt.setInt(2, curso.getId_descricao());
                    ResultSet rs = stmt.executeQuery();

                
                    System.out.println("entrou aki");
                    while (rs.next()){
                    if(rs.getInt("id_curso_s")==(curso.getId()) && rs.getInt("id_turma_s")==curso.getId_descricao()){
                     decisao=1;  
                        System.out.println("akiii");
                    }            
                    
                
            }
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
        
            if(decisao==1){
                System.out.println("nao encontrou");
                return true;
                
            }else{
                System.out.println("encontrou");
               return false; 
            }
    }

    public void DeletarCurso (Curso a) throws SQLException {
        if(HaPD(a)){
            if(QuantCurso(a)<2){
          String sql_2 = "delete  from prof_disc using disciplina_curso,curso where prof_disc.id_disc_pd=disciplina_curso.id_disciplina_dc and disciplina_curso.id_curso_dc=curso.id_curso  and curso.nome_curso=? ";
            try (PreparedStatement consulta_2 = (PreparedStatement) conexao.prepareStatement(sql_2)) {
                consulta_2.setString(1, a.getNome());
                
                consulta_2.execute();
            }
            }
        }else{
            System.out.println( "nao tem prof_disc para deletar");
        }
        
        if(HaHA(a)){
         String sql_2 = "delete from hora_aula using descricao,curso,professor_serie_turma,serie where curso.id_curso=descricao.id_curso_desc and serie.id_curso_s=curso.id_curso and serie.id_turma_s=descricao.id_desc and professor_serie_turma.id_serie_turma_pst=serie.id_serie and hora_aula.id_serie_turma_ha=serie.id_serie and curso.nome_curso=? and descricao.descricao_desc=?";
            try (PreparedStatement consulta_2 = (PreparedStatement) conexao.prepareStatement(sql_2)) {
                consulta_2.setString(1, a.getNome());
                consulta_2.setString(2, a.getDescrição());
                consulta_2.execute();
            }
        } else{
            System.out.println( "nao tem hora aula para deletar");
        }      
        if(HaPST(a)){
         String sql_3 = "delete from professor_serie_turma using descricao,curso,serie where curso.id_curso=descricao.id_curso_desc and serie.id_curso_s=curso.id_curso and serie.id_turma_s=descricao.id_desc and professor_serie_turma.id_serie_turma_pst=serie.id_serie and curso.nome_curso=? and descricao.descricao_desc=?";
            try (PreparedStatement consulta_3 = (PreparedStatement) conexao.prepareStatement(sql_3)) {
                consulta_3.setString(1, a.getNome());
                consulta_3.setString(2, a.getDescrição());
                consulta_3.execute();
            }
              
        }else{
            System.out.println( "nao tem profserieturma para deletar");
        } 
        if(IsUnicCurso(a)){
            
        }else{
        if(HaDC(a)){
            
         String sql_2 = "delete from disciplina using curso,disciplina_curso  where disciplina_curso.id_disciplina_dc=disciplina.id_disc and disciplina_curso.id_curso_dc=curso.id_curso  and curso.nome_curso=? ";
            try (PreparedStatement consulta_2 = (PreparedStatement) conexao.prepareStatement(sql_2)) {
                consulta_2.setString(1, a.getNome());
                
                consulta_2.execute();
            }
            
         String sql_1 = "delete from disciplina_curso using curso where disciplina_curso.id_curso_dc=curso.id_curso and curso.nome_curso=? ";
            try (PreparedStatement consulta_1 = (PreparedStatement) conexao.prepareStatement(sql_1)) {
                consulta_1.setString(1, a.getNome());
                
                consulta_1.execute();
            }
         
        }else{
            System.out.println( "nao tem disciplina curso para deletar");
        }    
        }
        if(HaS(a)){
         String sql_4 = "delete from serie using descricao,curso where curso.id_curso=descricao.id_curso_desc and serie.id_curso_s=curso.id_curso and serie.id_turma_s=descricao.id_desc and curso.nome_curso=? and descricao.descricao_desc=?";
            try (PreparedStatement consulta_4 = (PreparedStatement) conexao.prepareStatement(sql_4)) {
                consulta_4.setString(1, a.getNome());
                consulta_4.setString(2, a.getDescrição());
                consulta_4.execute();
            }
              
        }else{
            System.out.println( "nao tem serie para deletar");
        }       
        String sql_e = "delete from descricao where id_curso_desc=? and descricao_desc=?";
        try (PreparedStatement consulta_e = (PreparedStatement) conexao.prepareStatement(sql_e)) {
            consulta_e.setInt(1, a.getId());
            consulta_e.setString(2, a.getDescrição());
            consulta_e.execute();
        }
       
        
        
      String sql = "select * from curso,descricao where curso.id_curso=descricao.id_curso_desc and "
              + "curso.nome_curso like ?";
        String descricao=""; 
            PreparedStatement stmt;
        try {
            stmt = (PreparedStatement) conexao.prepareStatement(sql);
            stmt.setString(1, a.getNome());
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()){
                    descricao=rs.getString("descricao_desc");
                    
                }
            }
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
          if("".equals(descricao)){
       String sql_f = "delete from curso where nome_curso=? ";
        try {
                try (PreparedStatement consulta_f = (PreparedStatement) conexao.prepareStatement(sql_f)) {
                    consulta_f.setString(1, a.getNome());
                    consulta_f.execute();
                }
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }  
                }  
           
    }

    private boolean HaDC(Curso curso) {
      String sql = "select id_curso_dc from disciplina_curso where id_curso_dc=? ";
        int decisao=0;
        try {
            PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
            stmt.setInt(1, curso.getId());
            
                    ResultSet rs = stmt.executeQuery();

                
                    System.out.println("entrou aki");
                    while (rs.next()){
                    if(rs.getInt("id_curso_dc")==(curso.getId()) ){
                     decisao=1;  
                        System.out.println("akiii");
                    }            
                    
                
            }
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
        
            if(decisao==1){
                System.out.println("nao encontrou");
                return true;
                
            }else{
                System.out.println("encontrou");
               return false; 
            }  
    }

    private boolean HaHA(Curso curso) throws SQLException {
        listaS=BuscaIdST2(curso);
        Serie s=new Serie();
        int id_st;
        String sql = "select id_ha from hora_aula where id_serie_turma_ha=?";
        int decisao=0;
        for(int i=0;i<listaS.size();i++){
         s=listaS.get(i);
         id_st=s.getId();
      
            PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
            stmt.setInt(1, id_st);
           
                    ResultSet rs = stmt.executeQuery();

                    System.out.println("HA");
                    while (rs.next()){
                    
                     decisao++;  
                        System.out.println("akiii somou a decisão");
            }
        
    }
            if(decisao>=1){
                System.out.println(" encontrou turmas");
                return true;
                
            }else{
                System.out.println("naum encontrou");
               return false; 
            }  
    }

    private int BuscaIdST(Curso curso) {
        String sql = "select id_serie from serie where id_curso_s=? and id_turma_s=?";
        int id=0;
  
        try {
            PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql); 
            stmt.setInt(2, curso.getId_descricao());
            stmt.setInt(1, curso.getId());
            ResultSet rs = stmt.executeQuery();
                
                while (rs.next()){
                    Curso c = new Curso();
                    
                    c.setId(rs.getInt("id_serie"));
                    id=c.getId();
                    
                }
            
           
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
          return id;
    }

    private boolean HaPST(Curso a) {
        int id_st=BuscaIdST(a);
        String sql = "select id_prof_pst from professor_serie_turma where id_serie_turma_pst=?";
        int decisao=0;
        try {
            PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
            stmt.setInt(1, id_st);
           
                    ResultSet rs = stmt.executeQuery();

                
                    System.out.println("entrou aki");
                    while (rs.next()){
                    
                     decisao++;  
                        System.out.println("akiii");
                               
                    
                
            }
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
        
            if(decisao>=1){
                System.out.println(" encontrou");
                return true;
                
            }else{
                System.out.println("nao encontrou");
               return false; 
            }   
    }

    private boolean HaS(Curso a) {
        int id_st=0;
         id_st=BuscaIdST(a);
         System.out.println("id="+id_st);
        if(id_st==0){
           return false; 
        }else{
           return true; 
        }
    }

    private boolean BuscaSeJaTemEsseCurso(Curso curso) {
       String sql = "select nome_curso from curso where nome_curso=? ";
        int decisao=0;
        try {
            PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
            stmt.setString(1, curso.getNome());
            
                    ResultSet rs = stmt.executeQuery();

                
                    System.out.println("entrou aki");
                    while (rs.next()){
                    
                     decisao++;  
                        System.out.println("akiii");
                                
                    
                
            }
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
        
            if(decisao>=1){
                System.out.println("nao encontrou");
                return true;
                
            }else{
                System.out.println("encontrou");
               return false; 
            }  
    }

    
  public List<Curso> BuscarTodasDescriçoes() {
        String sql = "select DISTINCT descricao.descricao_desc,curso.nome_curso,descricao.id_desc from descricao,curso where descricao.id_curso_desc=curso.id_curso ";
         List<Curso> lista = new ArrayList<>();
         try {
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql); ResultSet rs = stmt.executeQuery()) {
                
                
                while (rs.next()) {
                    Curso c = new Curso();
                    c.setDescrição(rs.getString("descricao_desc"));
                   
                    c.setNome(rs.getString("nome_curso"));
                    
                     c.setId_descricao(rs.getInt("id_desc"));
                    lista.add(c);
                    
                }
            }
           
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
          return lista;
    }

    private void Altera1Opcao(Curso curso) {
        System.out.println("1opcao");
                        String sql=" update descricao set descricao_desc=? where id_curso_desc=? and id_desc=? ";
                try {
            try (PreparedStatement consulta = (PreparedStatement) conexao.prepareStatement(sql)) {
                consulta.setInt(2, curso.getId());
                consulta.setInt(3, curso.getId_descricao());
                consulta.setString(1, curso.getDescrição());
                consulta.execute();
            }
                } catch (SQLException ex) {
                    Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
                }
    }

    private void Altera2Opcao(Curso curso) throws SQLException {
         System.out.println("2opcao");
             if(HaMaisDumaLetra(curso)){
                 if(BuscaSeJaTemEsseCurso(curso)){
                    int id=BuscaId(curso.getNome());
                
                String sql_c = "insert into descricao (id_curso_desc,descricao_desc) values (?,?)";
                try (PreparedStatement stmt3 = (PreparedStatement) conexao.prepareStatement(sql_c)) {
                    stmt3.setInt(1, id);        
                    stmt3.setString(2, curso.getDescrição());       
                    stmt3.execute();
                }
                
                int id_desc=BuscaIdDescNovo(curso,id);
                if(HaS(curso)){
                String sql=" update serie set id_curso_s=? and id_turma_s=? where id_curso_s=? and id_turma_s=? ";
                try {
                    try (PreparedStatement consulta = (PreparedStatement) conexao.prepareStatement(sql)) {
                        consulta.setInt(2, id_desc);
                        consulta.setInt(1, id);
                        consulta.setInt(3, curso.getId_curso());
                        consulta.setInt(4, curso.getId_descricao());
                        consulta.execute();
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
                } 
                }
                if(HaDC(curso)){
                  String sql=" update disciplina_curso set id_curso_dc=? and id_descricao_dc=? where id_curso_dc=? and id_descricao_dc=? ";
                try {
                    try (PreparedStatement consulta = (PreparedStatement) conexao.prepareStatement(sql)) {
                        consulta.setInt(2, id_desc);
                        consulta.setInt(1, id);
                        consulta.setInt(3, curso.getId_curso());
                        consulta.setInt(4, curso.getId_descricao());
                        consulta.execute();
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
                }   
                }
                
                 String sql_e = "delete from descricao where id_curso_desc=? and descricao_desc=?";
                try (PreparedStatement consulta_e = (PreparedStatement) conexao.prepareStatement(sql_e)) {
                    consulta_e.setInt(1, curso.getId());
                    consulta_e.setString(2, curso.getDesc_ant());
                    consulta_e.execute();
                }
              JOptionPane.showMessageDialog(null, "ha mais de uma descricao,  ha este curso");
       }else{
               String sql_a = "insert into curso (nome_curso) values (?)";
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql_a)) {
                stmt.setString(1, curso.getNome());        
                stmt.execute();
            }
                
                int id=BuscaId(curso.getNome());
                
                String sql_c = "insert into descricao (id_curso_desc,descricao_desc) values (?,?)";
                try (PreparedStatement stmt3 = (PreparedStatement) conexao.prepareStatement(sql_c)) {
                    stmt3.setInt(1, id);        
                    stmt3.setString(2, curso.getDescrição());       
                    stmt3.execute();
                }
                 
                
              
                int id_desc=BuscaIdDescNovo(curso,id);
                if(HaS(curso)){
                String sql=" update serie set id_curso_s=? and id_turma_s=? where id_curso_s=? and id_turma_s=? ";
                try {
                    try (PreparedStatement consulta = (PreparedStatement) conexao.prepareStatement(sql)) {
                        consulta.setInt(2, id_desc);
                        consulta.setInt(1, id);
                        consulta.setInt(3, curso.getId_curso());
                        consulta.setInt(4, curso.getId_descricao());
                        consulta.execute();
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
                } 
                }
                if(HaDC(curso)){
                  String sql=" update disciplina_curso set id_curso_dc=?  where id_curso_dc=? and id_descricao_dc=? ";
                try {
                    try (PreparedStatement consulta = (PreparedStatement) conexao.prepareStatement(sql)) {
                        
                        consulta.setInt(1, id);
                        consulta.setInt(2, curso.getId_curso());
                        consulta.setInt(3, curso.getId_descricao());
                        consulta.execute();
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
                }   
                 String sql1=" update disciplina_curso set  id_descricao_dc=? where id_curso_dc=? and id_descricao_dc=? ";
                try {
                    try (PreparedStatement consulta = (PreparedStatement) conexao.prepareStatement(sql1)) {
                        consulta.setInt(1, id_desc);
                        
                        consulta.setInt(2, curso.getId_curso());
                        consulta.setInt(3, curso.getId_descricao());
                        consulta.execute();
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
                }   
                }
                
                String sql_e = "delete from descricao where id_curso_desc=? and descricao_desc=?";
                try (PreparedStatement consulta_e = (PreparedStatement) conexao.prepareStatement(sql_e)) {
                    consulta_e.setInt(1, curso.getId());
                    consulta_e.setString(2, curso.getDesc_ant());
                    consulta_e.execute();
                }
              
              JOptionPane.showMessageDialog(null, "ha mais de uma descricao, nao ha este curso");
                 }
                
              
             }else{
                 if(BuscaSeJaTemEsseCurso(curso)){
                    int id=BuscaId(curso.getNome());
                     String sql=" update descricao set id_curso_desc=? where id_curso_desc=? and descricao_desc=? ";
                try {
                    try (PreparedStatement consulta = (PreparedStatement) conexao.prepareStatement(sql)) {
                        consulta.setInt(2, curso.getId());
                        consulta.setInt(1, id);
                        consulta.setString(3, curso.getDesc_ant());
                        consulta.execute();
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
                } 
                 int id_desc=BuscaIdDesc(curso);
                if(HaS(curso)){
                String sql3=" update serie set id_curso_s=? and id_turma_s=? where id_curso_s=? and id_turma_s=? ";
                try {
                    try (PreparedStatement consulta = (PreparedStatement) conexao.prepareStatement(sql3)) {
                        consulta.setInt(2, id_desc);
                        consulta.setInt(1, id);
                        consulta.setInt(3, curso.getId_curso());
                        consulta.setInt(4, curso.getId_descricao());
                        consulta.execute();
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
                } 
                }
                if(HaDC(curso)){
                  String sql4=" update disciplina_curso set id_curso_dc=? and id_descricao_dc=? where id_curso_dc=? and id_descricao_dc=? ";
                try {
                    try (PreparedStatement consulta = (PreparedStatement) conexao.prepareStatement(sql4)) {
                        consulta.setInt(2, id_desc);
                        consulta.setInt(1, id);
                        consulta.setInt(3, curso.getId_curso());
                        consulta.setInt(4, curso.getId_descricao());
                        consulta.execute();
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
                }   
                }
                String sql_f = "delete from curso where id_curso=? ";
        try {
                        try (PreparedStatement consulta_f = (PreparedStatement) conexao.prepareStatement(sql_f)) {
                            consulta_f.setInt(1, curso.getId());
                            consulta_f.execute();
                        }
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }  
        
                 }else{
                String sql=" update curso set nome_curso=? where id_curso=? ";
                try {
                    try (PreparedStatement consulta = (PreparedStatement) conexao.prepareStatement(sql)) {
                        consulta.setInt(2, curso.getId());
                        consulta.setString(1, curso.getNome());
                        consulta.execute();
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
                } 
                 }
             }
    }

    private void Altera3Opcao(Curso curso) throws SQLException {
          System.out.println("3opcao");
            if(HaMaisDumaLetra(curso)){
                 System.out.println("4opcao");
                if(ONomeExiste(curso)){
                     System.out.println("5opcao");
                    int id=BuscaId(curso.getNome());
                    String sql_c = "insert into descricao (id_curso_desc,descricao_desc) values (?,?)";
                    try (PreparedStatement stmt3 = (PreparedStatement) conexao.prepareStatement(sql_c)) {
                        stmt3.setInt(1, id);        
                        stmt3.setString(2, curso.getDescrição());       
                        stmt3.execute();
                    }
                    
                 int id_descant=BuscaIdDesc(curso);   
                 int id_desc=BuscaIdDescNovo(curso,id);  
                            if(HouverSerie(curso)){
                            String sql3=" update serie set id_curso_s=? and id_turma_s=? where id_curso_s=? and id_turma_s=? ";
                            try {
                                try (PreparedStatement consulta2 = (PreparedStatement) conexao.prepareStatement(sql3)) {
                                    consulta2.setInt(1, id);
                                    consulta2.setInt(2, id_desc);
                                    consulta2.setInt(3, curso.getId());
                                    consulta2.setInt(4, curso.getId_descricao());
                                    consulta2.execute();
                                }
                            } catch (SQLException ex) {
                                Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
                            }

                            }       
                                String sql_f = "delete from descricao where id_desc=? ";
                        try {
                        try (PreparedStatement consulta_f = (PreparedStatement) conexao.prepareStatement(sql_f)) {
                            consulta_f.setInt(1, id_descant);
                            consulta_f.execute();
                        }
                        } catch (SQLException ex) {
                            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
                        }  

                   }else{
                   System.out.println("6opcao");  
                 String sql_a = "insert into curso (nome_curso) values (?)";
                try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql_a)) {
                    stmt.setString(1, curso.getNome());        
                    stmt.execute();
                }
                 
                 int id=BuscaId(curso.getNome());
                 int id_ant=BuscaId(curso.getNome_ant());
                 String sql=" update descricao set id_curso_desc=?  where id_curso_desc=? and descricao_desc=? ";
                try {
                    try (PreparedStatement consulta = (PreparedStatement) conexao.prepareStatement(sql)) {
                        consulta.setInt(2, curso.getId());
                        consulta.setInt(1, id);
                        consulta.setString(3, curso.getDesc_ant());
                        consulta.execute();
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
                }
                
                String sql2=" update descricao set descricao_desc=? where id_curso_desc=? and descricao_desc=? ";
                try {
                    try (PreparedStatement consulta2 = (PreparedStatement) conexao.prepareStatement(sql2)) {
                        consulta2.setString(1, curso.getDescrição());
                        consulta2.setInt(2, id);
                        consulta2.setString(3, curso.getDesc_ant());
                        consulta2.execute();
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
                }
                if(HouverSerie(curso)){
                String sql3=" update serie set id_curso_s=? where id_curso_s=? and id_turma_s=? ";
                try {
                    try (PreparedStatement consulta2 = (PreparedStatement) conexao.prepareStatement(sql3)) {
                        consulta2.setInt(1, id);
                        consulta2.setInt(2, id_ant);
                        consulta2.setInt(3, curso.getId_descricao());
                        consulta2.execute();
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
                }
                
                }    
                }
                
                }else{
                if(ONomeExiste(curso)){
                 int id=BuscaId(curso.getNome());   
                 
                  String sql2=" update descricao set descricao_desc=? where id_curso_desc=? and descricao_desc=? ";
                try {
                    try (PreparedStatement consulta2 = (PreparedStatement) conexao.prepareStatement(sql2)) {
                        consulta2.setString(1, curso.getDescrição());
                        consulta2.setInt(2, curso.getId());
                        consulta2.setString(3, curso.getDesc_ant());
                        consulta2.execute();
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
                }
                 
                 String sql=" update descricao set id_curso_desc=?  where id_curso_desc=? and descricao_desc=? ";
                try {
                    try (PreparedStatement consulta = (PreparedStatement) conexao.prepareStatement(sql)) {
                        consulta.setInt(2, curso.getId());
                        consulta.setInt(1, id);
                        consulta.setString(3, curso.getDescrição());
                        consulta.execute();
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
                }
                
               
                
                if(HouverSerie(curso)){
                String sql3=" update serie set id_curso_s=? where id_curso_s=? and id_turma_s=? ";
                try {
                    try (PreparedStatement consulta2 = (PreparedStatement) conexao.prepareStatement(sql3)) {
                        consulta2.setInt(1, id);
                        consulta2.setInt(2, curso.getId());
                        consulta2.setInt(3, curso.getId_descricao());
                        consulta2.execute();
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
                }
                
                } 
                
                 String sql_f = "delete from curso where nome_curso=? ";
        try {
                    try (PreparedStatement consulta_f = (PreparedStatement) conexao.prepareStatement(sql_f)) {
                        consulta_f.setString(1, curso.getNome_ant());
                        consulta_f.execute();
                    }
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        
                }else{
                 System.out.println("7opcao");
                String sql=" update curso set nome_curso=? where id_curso=? ";
                try {
                    try (PreparedStatement consulta = (PreparedStatement) conexao.prepareStatement(sql)) {
                        consulta.setInt(2, curso.getId());
                        consulta.setString(1, curso.getNome());
                        consulta.execute();
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
                }
                
                String sql2=" update descricao set descricao_desc=? where id_desc=? ";
                try {
                    try (PreparedStatement consulta2 = (PreparedStatement) conexao.prepareStatement(sql2)) {
                        consulta2.setInt(2, curso.getId_descricao());
                        consulta2.setString(1, curso.getDescrição());
                        consulta2.execute();
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
                }   
                }
                
            }
    }

    private boolean HaPD(Curso a) {
        
        String sql = "select id_disc_pd from prof_disc,disciplina_curso,curso where prof_disc.id_disc_pd=disciplina_curso.id_disciplina_dc and disciplina_curso.id_curso_dc=curso.id_curso  and curso.nome_curso=?";
        int decisao=0;
        try {
            PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
            stmt.setString(1, a.getNome());
            
           
                    ResultSet rs = stmt.executeQuery();

                
                    System.out.println("entrou aki");
                    while (rs.next()){
                    
                     decisao++;  
                        System.out.println("akiii");
                               
                    
                
            }
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
        
            if(decisao>=1){
                System.out.println(" encontrou um prof disc");
                return true;
                
            }else{
                System.out.println("nao encontrou");
               return false; 
            }   
    }

    public List<Curso> BuscarDescricaoporSerie(String nome) {
        String sql = "select descricao.descricao_desc,descricao.id_desc from descricao,serie where serie.id_turma_s=descricao.id_desc and serie.nome_serie=? order by descricao.descricao_desc";
         List<Curso> lista = new ArrayList<>();
         try {
           PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
                 stmt.setString(1, nome);   
                    ResultSet rs = stmt.executeQuery(); 
                
                
                while (rs.next()) {
                    Curso c = new Curso();
                    c.setDescrição(rs.getString("descricao_desc"));
                    c.setId_descricao(rs.getInt("id_desc"));
                    
                    lista.add(c);
                    
                }
            
           
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
          return lista;
    }

    private List<Serie> BuscaIdST2(Curso curso) {
       String sql = "select id_serie from serie where id_curso_s=? and id_turma_s=?";
         List<Serie> lista = new ArrayList<>();
         try {
           PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
                 stmt.setInt(1, curso.getId_curso());
                 stmt.setInt(2, curso.getId_descricao());
                    ResultSet rs = stmt.executeQuery(); 
                
                
                while (rs.next()) {
                    Serie s = new Serie();
                    
                    s.setId(rs.getInt("id_serie"));
                    
                    lista.add(s);
                    
                }
            
           
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
          return lista; 
    }

    private boolean IsUnicCurso(Curso a) {
        String sql = "select descricao.id_desc from curso,descricao where descricao.id_curso_desc=curso.id_curso and curso.nome_curso=?";
        int decisao=0;
        try {
            PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
            stmt.setString(1, a.getNome());
            
           
                    ResultSet rs = stmt.executeQuery();

                
                    System.out.println("entrou aki");
                    while (rs.next()){
                    
                     decisao++;  
                        System.out.println("akiii");
                               
                    
                
            }
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
        
            if(decisao>1){
                System.out.println("existem 2 cursos");
                return true;
                
            }else{
                System.out.println("existem 1 cursos");
               return false; 
            }   
    }

    private int QuantCurso(Curso curso) {
        String sql = "select count(descricao.id_desc) from curso,descricao where curso.id_curso=descricao.id_curso_desc and nome_curso=?";
        int id=0;
  
        try {
            PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql); 
            
            stmt.setString(1, curso.getNome());
            ResultSet rs = stmt.executeQuery();
            while(rs.next()){
                    id=rs.getInt("count");
            }      
               
            
           
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
          return id;
        
    }
}
